﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace QMSDAL
{
    public class GCBH
    {
        //获取连接字符串
        public static string cnstr = ConfigurationManager.ConnectionStrings["GCBH"].ConnectionString.ToString();
        
        /// <summary>
        /// 获取所有的工程编号、机型、机号
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllGCBH()
        {
            string strSQL = "SELECT gcbh, jx, tf FROM GCBH_D";
            return GetAllGCBH(strSQL);
        }

        /// <summary>
        /// 获取工程编号、机型、机号的公用函数
        /// </summary>
        /// <returns></returns>
        private static DataSet GetAllGCBH(string strSQL)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(cnstr))
            {
                cn.Open();
                SqlDataAdapter cmd = new SqlDataAdapter(strSQL, cn);
                cmd.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// 获取所有的工程编号
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllProjectNumbers()
        {
            string strSQL = "SELECT DISTINCT gcbh FROM GCBH_D";
            DataSet ds = GetAllGCBH(strSQL);
            return ds;
        }

        /// <summary>
        /// 获取所有机型
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllJX()
        {
            string strSQL = "SELECT DISTINCT jx FROM GCBH_D";
            return GetAllGCBH(strSQL);
        }

        /// <summary>
        /// 获取所有机号
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllTF()
        {
            string strSQL = "SELECT DISTINCT tf FROM GCBH_D";
            return GetAllGCBH(strSQL);
        }
        /// <summary>
        /// 根据工程编号获取机型
        /// </summary>
        /// <returns></returns>
        public static string GetJXGCBH(string gcbh)
        {
            using (SqlConnection cn = new SqlConnection(cnstr))
            {
                cn.Open();
                string strSQL = "SELECT jx FROM GCBH_D WHERE gcbh = '" + gcbh + "'";
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                string strId = cmd.ExecuteScalar().ToString();  //获取机号
                return strId;
            }
        }

        /// <summary>
        /// 根据工程编号获取机号
        /// </summary>
        /// <param name="gcbh"></param>
        /// <returns></returns>
        public static string GetTFGCBH(string gcbh)
        {
            using (SqlConnection cn = new SqlConnection(cnstr))
            {
                cn.Open();
                string strSQL = "SELECT tf FROM GCBH_D WHERE gcbh = '" + gcbh + "'";
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                string str = cmd.ExecuteScalar().ToString();    //获取机型
                return str;
            }
        }

        /// <summary>
        /// 根据机型找相应的工程编号
        /// </summary>
        /// <param name="jx"></param>
        /// <returns></returns>
        public static List<string> GetGCBHbyJX(string jx)
        {
            List<string> strJXs = new List<string>();
            using (SqlConnection cn = new SqlConnection(cnstr))
            {
                cn.Open();
                string strSQL = "SELECT gcbh FROM GCBH_D WHERE jx = '" +
                    jx + "'";
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                SqlDataReader myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    strJXs.Add(myReader["gcbh"].ToString());
                }
            }
            return strJXs;
        }

        /// <summary>
        /// 根据机号找相应的工程编号
        /// </summary>
        /// <param name="tf"></param>
        /// <returns></returns>
        public static List<string> GetGCBHbyTF(string tf)
        {
            List<string> strTFs = new List<string>();
            using (SqlConnection cn = new SqlConnection(cnstr))
            {
                cn.Open();
                string strSQL = "SELECT gcbh FROM GCBH_D WHERE tf = '" +
                    tf + "'";
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                SqlDataReader myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    strTFs.Add(myReader["gcbh"].ToString());
                }
            }
            return strTFs;
        }
    }
}
